package com.ocm.tourism.core.dao.jpa;

import java.util.List;

import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.ocm.tourism.core.dao.HotelDao;
import com.ocm.tourism.core.domain.Advertisement;
import com.ocm.tourism.core.domain.BusinessType;
import com.ocm.tourism.core.domain.Distance;
import com.ocm.tourism.core.domain.Hotel;
import com.ocm.tourism.core.domain.MapInfo;
import com.ocm.tourism.core.domain.Room;
import com.ocm.tourism.core.domain.SaleOff;
import com.ocm.tourism.core.model.FieldCriteria;
import com.ocm.tourism.core.model.SearchCriteria;
import com.ocm.tourism.core.search.build.SQLBuilder;

@Repository
public class JpaHotelDao extends JpaGenericDao<Hotel> implements HotelDao {

   SQLBuilder sqlBuilder;

   @Autowired
   public JpaHotelDao(@Qualifier("hotelSQLBuilder")SQLBuilder sqlBuilder) {
      this.setClazz(Hotel.class);
      this.sqlBuilder = sqlBuilder;
   }

   @Override
   public long count() {
      Query query = this.entityManager().createQuery("SELECT COUNT(b) FROM Hotel b WHERE b.businessType =:businessType", Long.class);
      query.setParameter("businessType", BusinessType.HOTEL);
      long count = (Long) query.getSingleResult();
      return count;
   }

   @Override
   public long count(long participantId) {
      Query query = this.entityManager().createQuery("SELECT COUNT(b) FROM Hotel b WHERE b.businessType =:businessType and b.participant.id =:participantId", Long.class);
      query.setParameter("businessType", BusinessType.HOTEL);
      query.setParameter("participantId", participantId);
      long count = (Long) query.getSingleResult();
      return count;
   }

   @SuppressWarnings("unchecked")
   public List<Hotel> findAll() {
      Query query = this.entityManager().createQuery("SELECT b FROM Hotel b WHERE b.businessType =:businessType", Hotel.class);
      query.setParameter("businessType", BusinessType.HOTEL);
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   @SuppressWarnings("unchecked")
   public List<Hotel> findAll(List<Long> hotelIds) {
      Query query = this.entityManager().createQuery("SELECT b FROM Hotel b WHERE b.businessType =:businessType AND b.id IN (:hotels)", Hotel.class);
      query.setParameter("businessType", BusinessType.HOTEL);
      query.setParameter("hotels", hotelIds);
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   @SuppressWarnings("unchecked")
   public List<Hotel> findAll(int firstResult, int maxResults, long participantId) {
      Query query = this.entityManager().createQuery("SELECT b FROM Hotel b WHERE b.businessType =:businessType and b.participant.id =:participantId", Hotel.class).setFirstResult(firstResult).setMaxResults(maxResults);
      query.setParameter("businessType", BusinessType.HOTEL);
      query.setParameter("participantId", participantId);
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   @SuppressWarnings("unchecked")
   public List<Hotel> findAll(int firstResult, int maxResults) {
      Query query = this.entityManager().createQuery("SELECT b FROM Hotel b WHERE b.businessType =:businessType", Hotel.class).setFirstResult(firstResult).setMaxResults(maxResults);
      query.setParameter("businessType", BusinessType.HOTEL);
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   public long countRooms(long hotel) {
      Query query = this.entityManager().createQuery("SELECT count(r) FROM Room r WHERE r.hotel.id =:hotel", Long.class);
      query.setParameter("hotel", hotel);
      long countRooms = (Long) query.getSingleResult();
      return countRooms;
   }
   
   @SuppressWarnings("unchecked")
   public List<Room> findAllRooms(long hotel, int firstResult, int maxResults) {
      Query query = this.entityManager().createQuery("SELECT r FROM Room r WHERE r.hotel.id =:hotel", Room.class).setFirstResult(firstResult).setMaxResults(maxResults);
      query.setParameter("hotel", hotel);
      List<Room> rooms = query.getResultList();
      return rooms;
   }

   @SuppressWarnings("unchecked")
   public List<Room> findAllRooms(long hotel) {
      Query query = this.entityManager().createQuery("SELECT r FROM Room r WHERE r.hotel.id =:hotel", Room.class);
      query.setParameter("hotel", hotel);
      List<Room> rooms = query.getResultList();
      return rooms;
   }

   public Room findRoom(long roomId) {
      Room room = this.entityManager().find(Room.class, roomId);
      return room;
   }
   
   public long countSaleOffs(long hotel) {
      Query query = this.entityManager().createQuery("SELECT count(r) FROM SaleOff r WHERE r.hotel.id =:hotel", Long.class);
      query.setParameter("hotel", hotel);
      long countRooms = (Long) query.getSingleResult();
      return countRooms;
   }

   @SuppressWarnings("unchecked")
   public List<SaleOff> findSaleOffs(long hotel, int firstResult, int maxResults) {
      Query query = this.entityManager().createQuery("SELECT r FROM SaleOff r WHERE r.hotel.id =:hotel", SaleOff.class).setFirstResult(firstResult).setMaxResults(maxResults);
      query.setParameter("hotel", hotel);
      List<SaleOff> saleOffs = query.getResultList();
      return saleOffs;
   }

   @Override
   public Advertisement findAdvertisement(long advertisementId) {
      Advertisement advertisements = this.entityManager().find(Advertisement.class, advertisementId);
      return advertisements;
   }

   @Override
   public SaleOff findSaleOff(long saleOffId) {
      SaleOff saleOff = this.entityManager().find(SaleOff.class, saleOffId);
      return saleOff;
   }
   
   @SuppressWarnings("unchecked")
   public List<Hotel> findHotels(List<FieldCriteria> criterias) {
      String sql = "SELECT b FROM Hotel b WHERE b.businessType =:businessType";
      for (FieldCriteria criteria : criterias){
         sql = criteria.appendCritera(sql);
      }
      Query query = this.entityManager().createQuery(sql, Hotel.class);
      query.setParameter("businessType", BusinessType.HOTEL);
      for (FieldCriteria criteria : criterias){
         query.setParameter(criteria.getName(), criteria.getValue());
      }
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   @SuppressWarnings("unchecked")
   public List<Distance> findDistances(long hotel) {
      Query query = this.entityManager().createQuery("SELECT r FROM Distance r WHERE r.hotel.id =:hotel", Distance.class);
      query.setParameter("hotel", hotel);
      List<Distance> distances = query.getResultList();
      return distances;
   }

   public MapInfo findMap(long hotel){
       return find(hotel).getMapInfo();
   }

   @SuppressWarnings("unchecked")
   @Override
   public List<Hotel> findHotels(SearchCriteria searchCriteria, int firstResult,int maxResults) {
      Query query = this.entityManager().createQuery(sqlBuilder.buildSQL(searchCriteria), Hotel.class).setFirstResult(firstResult).setMaxResults(maxResults);
      List<FieldCriteria> criterias = searchCriteria.getFieldCriterias();
      for (FieldCriteria criteria : criterias){
         query.setParameter(criteria.getName(), criteria.getValue());
      }
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   @SuppressWarnings("unchecked")
   @Override
   public List<Hotel> findAdvs(int firstResult, int maxResults) {
      Query query = this.entityManager().createNativeQuery("SELECT b.* FROM Business b inner join Advertisement a ON b.advertisement=a.id LEFT JOIN Audit_Business_Advs au ON b.id=au.foreign_key WHERE b.business_type = 'HOTEL' " +
            "and ((a.start_date <= getdate() and a.end_date >= getdate() and a.forever = 'false') or a.forever = 'true') and a.actived='true' order by au.last_index asc, a.modified_date, a.end_date, a.start_date desc", Hotel.class).setFirstResult(firstResult).setMaxResults(maxResults);
      List<Hotel> businesses = query.getResultList();
      return businesses;
   }

   @Override
   public long countAdvs() {
      Query query = this.entityManager().createQuery("SELECT COUNT(b) FROM Hotel b join b.advertisement a WHERE b.businessType =:businessType " +
            "and ((a.startDate <= current_date() and a.endDate >= current_date() and a.forever = false) or a.forever = true) and a.actived=true", Long.class);
      query.setParameter("businessType", BusinessType.HOTEL);
      long count = (Long) query.getSingleResult();
      return count;
   }
}
